import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import tcube.record.CheckInRecord;
import tcube.record.DateRecord;
import tcube.record.FactTableRecord;
import tcube.record.LocTableRecord;
import tcube.util.CubeUtil;

import com.google.gson.Gson;
import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;

public class TweetCube {

	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static String DB_URL;
	private Connection conn = null;
	private int request_cnt;

	Map<String, ArrayList<String>> hierarchy = new HashMap<String, ArrayList<String>>();
	Map<String, String> idNameMap = new HashMap<String, String>();

	List<String> tempList = new ArrayList<String>();
	int cnt = 0;
	int maxLevel = 0;

	public TweetCube(String DB_URL) {
		try {
			this.DB_URL = DB_URL;
			this.conn = getConnection();
			this.request_cnt = 0;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	private void dfs(JsonObject parent, String preId) {
		JsonArray categories = parent.getAsJsonArray("categories");
		if (categories == null || categories.size() == 0) {
			// hierarchy.put(preId, new ArrayList<String>(tempList));
			// System.out.println(tempList);
			if (tempList.size() > maxLevel) {
				maxLevel = tempList.size();
			}
			cnt++;
			return;
		}
		for (int i = 0; i < categories.size(); i++) {
			JsonObject catObj = categories.get(i).getAsJsonObject();
			String catId = catObj.get("id").getAsString();
			String catName = catObj.get("name").getAsString();
			idNameMap.put(catId, catName);
			tempList.add(catId);
			hierarchy.put(catId, new ArrayList<String>(tempList));
			System.out.println(catId + ":" + tempList);
			dfs(catObj, catId);
			tempList.remove(catId);
		}

	}

	public void calCategory() throws IOException {
		JsonParser parser = new JsonParser();

		// String catText = CubeUtil
		// .HttpGetContent(
		// new URL(
		// "https://api.foursquare.com/v2/venues/categories?oauth_token=UINIO453V10B5B22RB33ODC3SNKBMUZTT5KLPMUCUAYAHFDR&v=20121221"),
		// "GET");

		FileReader fr = new FileReader("categories.json");
		BufferedReader br = new BufferedReader(fr);
		String catText = br.readLine();
		System.out.println("text " + catText);

		JsonObject rootObj = parser.parse(catText).getAsJsonObject();
		tempList.clear();
		dfs(rootObj.get("response").getAsJsonObject(), null);

		System.out.println("cnt" + cnt);
		System.out.println("maxLev:" + maxLevel);
	}

	private int tweetInsert(Connection conn, String id, String tweets)
			throws SQLException {
		String insertSql = "INSERT IGNORE INTO idToTweets (id,tweet) VALUES"
				+ "(?,?)";
		PreparedStatement insertStatement = conn.prepareStatement(insertSql);
		insertStatement.setString(1, id);
		insertStatement.setString(2, tweets);

		return insertStatement.executeUpdate();
	}

	public void preprocess() throws IOException, ClassNotFoundException,
			SQLException, InterruptedException {

		// this.conn = getConnection();

		FileReader freader = new FileReader(
				"U:\\datasets\\4sq\\4sqdata1\\1345781446271.json");
		BufferedReader br = new BufferedReader(freader);
		String s;

		Gson gson = new Gson();
		JsonParser parser = new JsonParser();
		int cnt = 0;
		while ((s = br.readLine()) != null) {
			JsonObject obj = parser.parse(s).getAsJsonObject();
			if (obj.get("id_str") == null || obj.get("text") == null) {
				continue;
			}
			String tweet_id = obj.get("id_str").getAsString();
			String tweet_text = obj.get("text").getAsString();

			String lat = null;
			String lng = null;

			if (!obj.get("coordinates").isJsonNull()) {
				lat = obj.get("coordinates").getAsJsonObject()
						.getAsJsonArray("coordinates").get(0).getAsString();
				lng = obj.get("coordinates").getAsJsonObject()
						.getAsJsonArray("coordinates").get(1).getAsString();
				// System.out.println(lat+lng);
				insertLL(conn, lat, lng, tweet_id);
			}

			if (tweetInsert(conn, tweet_id, tweet_text) == 0) {
				System.out.println("Already exsits");
				continue;
			}

			String date = obj.get("created_at").getAsString();
			String[] times = date.split("\\s");
			String year = times[times.length - 1];
			String month = times[1];
			String day = times[2];

			DateRecord dateRecord = new DateRecord(year, month, day);

			System.out.println(year + month + day);

			// obj.get("coordinates");
			System.out.println("oriTweet" + s);
			if (obj.get("entities").getAsJsonObject().getAsJsonArray("urls") == null
					|| obj.get("entities").getAsJsonObject()
							.getAsJsonArray("urls").size() == 0) {
				System.out.println("No url");
				continue;
			}

			String url_text = null;
			try {
				url_text = obj.get("entities").getAsJsonObject()
						.getAsJsonArray("urls").get(0).getAsJsonObject()
						.get("expanded_url").getAsString();
			} catch (Exception e) {
				e.printStackTrace();
				continue;
			}

			URL url = new URL(
					"https://api-ssl.bitly.com/v3/expand?format=json&login=o_3p180gdf11&apiKey=R_3450e9a6a91b5a3de8bac7b479c39fdf&shortUrl="
							+ url_text);
			System.out.println(url_text);
			// int cnt = 1;
			String urlResponse = CubeUtil.HttpGetContent(url, "GET");

			System.out.println(urlResponse);

			if (!parser.parse(urlResponse.trim()).getAsJsonObject().get("data")
					.getAsJsonObject().getAsJsonArray("expand").get(0)
					.getAsJsonObject().has("long_url")) {
				System.out.println("No long url");
				continue;
			}
			String longurl = parser.parse(urlResponse.trim()).getAsJsonObject()
					.get("data").getAsJsonObject().getAsJsonArray("expand")
					.get(0).getAsJsonObject().get("long_url").getAsString();
			int st = longurl.indexOf("checkin");
			if (st == -1) {
				System.out.println("Url type mismatch");
				continue;
			}
			st += 8;
			int ed = longurl.indexOf("?s=");
			if (ed == -1) {
				System.out.println("Url type mismatch");
				continue;
			}
			String checkin = longurl.substring(st, ed);
			System.out.println("ch " + checkin);
			st = ed + 3;
			ed = longurl.indexOf("&ref");
			if (ed == -1) {
				System.out.println("Url type mismatch");
				continue;
			}
			String signature = longurl.substring(st, ed);
			System.out.println("sig " + signature);

			URL squareurl = new URL(
					"https://api.foursquare.com/v2/checkins/"
							+ checkin
							+ "?signature="
							+ signature
							+ "&client_id=IPL5EIBK4GKYQ0CNPMIEPJD2BBQZN1WCPRLFZRTYKIBCOODK&client_secret=J0O1ATSOS3G4RHSUHPRJ3GUTL1AA3OGRJYX4ZLUBIP1U3G3V&&v=20121219");
			System.out.println(squareurl.toString());

			String squareResponce = CubeUtil.HttpGetContent(squareurl, "GET");

			if (squareResponce == null) {
				System.out.println("Respone error");
				continue;
			}

			System.out.println(squareResponce);

			JsonObject venueObj = null;
			try {
				venueObj = parser.parse(squareResponce).getAsJsonObject()
						.get("response").getAsJsonObject().get("checkin")
						.getAsJsonObject().get("venue").getAsJsonObject();
			} catch (NullPointerException ex) {
				ex.printStackTrace();
				continue;
			}

			// /////////////////////////////////////////////////////////////////////
			JsonArray categories = venueObj.getAsJsonArray("categories");
			if (categories.size() == 0) {
				System.out.println("No categories");
				continue;
			}

			ArrayList<String> hList = null;
			try {
				String checkInId = categories.get(0).getAsJsonObject()
						.get("id").getAsString();
				hList = hierarchy.get(checkInId);

			} catch (Exception e) {
				e.printStackTrace();
				continue;
			}

			CheckInRecord checkInRecord = null;
			if (hList.size() == 1) {
				checkInRecord = new CheckInRecord(hList.get(0), "empty",
						"empty", idNameMap.get(hList.get(0)), "empty", "empty");
			} else if (hList.size() == 2) {
				checkInRecord = new CheckInRecord(hList.get(0), hList.get(1),
						"empty", idNameMap.get(hList.get(0)),
						idNameMap.get(hList.get(1)), "empty");
			} else if (hList.size() == 3) {
				checkInRecord = new CheckInRecord(hList.get(0), hList.get(1),
						hList.get(2), idNameMap.get(hList.get(0)),
						idNameMap.get(hList.get(1)),
						idNameMap.get(hList.get(2)));
			}

			if (!venueObj.get("location").getAsJsonObject().has("city")
					|| !venueObj.get("location").getAsJsonObject()
							.has("country")) {
				System.out.println("No country or city");
				continue;
			}
			String city = venueObj.get("location").getAsJsonObject()
					.get("city").getAsString();

			String country = venueObj.get("location").getAsJsonObject()
					.get("country").getAsString();

			LocTableRecord locRecord = new LocTableRecord(country, city);

			FactTableRecord factRecord = new FactTableRecord(
					dateRecord.getdId(), locRecord.getLocId(),
					checkInRecord.getcId(), tweet_id);

			recordUpdate(factRecord, checkInRecord, locRecord, dateRecord);

			// ///////////////----------------save file
			FileWriter fr = new FileWriter(
					"U:\\datasets\\4sqOri\\4sqdata\\13457814462710ori.json",
					true);
			BufferedWriter bw = new BufferedWriter(fr);
			bw.write(squareResponce.trim() + "\n");
			cnt++;
			bw.close();

			// if (cnt > 1) {
			// break;
			//
			// }

		}
		// conn.commit();

		this.conn.close();
	}

	public Connection getConnection() throws ClassNotFoundException,
			SQLException {

		Class.forName("com.mysql.jdbc.Driver");
		// STEP 3: Open a connection
		System.out.println("Connecting to a selected database...");
		conn = DriverManager.getConnection(DB_URL);
		return conn;
	}

	private void recordUpdate(FactTableRecord factRecord,
			CheckInRecord checkRecord, LocTableRecord locRecord,
			DateRecord dateRecord) throws SQLException {

		String selectFact = "SELECT * from factTable WHERE dateId= ? and locId = ? AND checkId = ? ";
		String insertFact = "INSERT IGNORE INTO factTable (checkId,locId,dateId,tweets,count) VALUES"
				+ "(?,?,?,?,?)";
		// String updateFact = "UPDATE factTable SET tweets = ? where ";

		String checkSql = "INSERT IGNORE INTO checkInTable (checkInId,level0Id,level1Id,level2Id,level0Label,level1Label,level2Label) VALUES"
				+ "(?,?,?,?,?,?,?)";
		String locSql = "INSERT IGNORE INTO locTable (locId,country,city) VALUES"
				+ "(?,?,?)";
		String dateSql = "INSERT IGNORE INTO dateTable (dateId,year,month,day) VALUES"
				+ "(?,?,?,?)";

		PreparedStatement selectFactStament = conn.prepareStatement(selectFact,
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
		selectFactStament.setInt(1, factRecord.getDateId());
		selectFactStament.setInt(2, factRecord.getLocId());
		selectFactStament.setInt(3, factRecord.getCheckInId());

		ResultSet rs = selectFactStament.executeQuery();
		if (rs.next()) {
			String tweets = rs.getString("tweets");
			int cnt = rs.getInt("count");
			tweets += ("," + factRecord.getTweetId());
			rs.updateString("tweets", tweets);
			rs.updateInt("count", cnt + 1);
			rs.updateRow();
		} else {
			PreparedStatement insertFactStatement = conn
					.prepareStatement(insertFact);
			insertFactStatement.setInt(1, factRecord.getCheckInId());
			insertFactStatement.setInt(2, factRecord.getLocId());
			insertFactStatement.setInt(3, factRecord.getDateId());
			insertFactStatement.setString(4, factRecord.getTweetId());
			insertFactStatement.setInt(5, 1);
			insertFactStatement.executeUpdate();

			PreparedStatement checkStatement = conn.prepareStatement(checkSql);
			PreparedStatement locStatement = conn.prepareStatement(locSql);
			PreparedStatement dateStatement = conn.prepareStatement(dateSql);

			checkStatement.setInt(1, checkRecord.getcId());
			checkStatement.setString(2, checkRecord.getLevel0());
			checkStatement.setString(3, checkRecord.getLevel1());
			checkStatement.setString(4, checkRecord.getLevel2());
			checkStatement.setString(5, checkRecord.getLevel0Label());
			checkStatement.setString(6, checkRecord.getLevel1Label());
			checkStatement.setString(7, checkRecord.getLevel2Label());

			locStatement.setInt(1, locRecord.getLocId());
			locStatement.setString(2, locRecord.getCountry());
			locStatement.setString(3, locRecord.getCity());

			dateStatement.setInt(1, dateRecord.getdId());
			dateStatement.setString(2, dateRecord.getYear());
			dateStatement.setString(3, dateRecord.getMonth());
			dateStatement.setString(4, dateRecord.getDay());

			checkStatement.executeUpdate();
			locStatement.executeUpdate();
			dateStatement.executeUpdate();

		}

	}

	private void insertLL(Connection conn, String lag, String lng, String tid)
			throws SQLException {
		String selSql = "select * from cube.idtotweets where id='" + tid + "'";
		// System.out.println(selSql);
		PreparedStatement stmt = conn.prepareStatement(selSql,
				ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
		ResultSet rs = stmt.executeQuery();
		if (rs.next()) {
			String LL = rs.getString("locLL");
			// System.out.print(LL);
			if (LL == null || LL.trim().isEmpty() || LL.equals("null")) {
				rs.updateString("locLL", lag + "," + lng);
				rs.updateRow();
				// System.out.println("locLL"+lag+","+lng);
			}
		}

	}

}